﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml.Serialization;
using System.IO;

namespace COMSQLClient
{
    /// <summary>
    /// Library that enables SQL Access.
    /// This library must be installed on a local maching and
    /// registered using RegAsm
    /// </summary>
    public class COMSqlDatabase
    {
        public COMSqlDatabase() { }

        /// <summary>
        /// Execute non query command against a database
        /// </summary>
        /// <param name="connectionString">Connection string to use to connect to a database</param>
        /// <param name="commandText">Query text</param>
        /// <param name="commandTypeString">Type of command as a string</param>
        /// <param name="parameterNames">Array of parameters names</param>
        /// <param name="parameterValues">Array of parameter values</param>
        /// <returns>Integer that indicates the number of rows affected</returns>
        public int ExecuteNonQuery(
            string connectionString, string commandText, string commandTypeString,
            string parameterNames, object[] parameterValues)
        {
            CommandType commandType = (CommandType)Enum.Parse(typeof(CommandType), commandTypeString);
            int returnValue = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (SqlCommand command = sqlConnection.CreateCommand())
                {
                    command.CommandType = commandType;
                    command.CommandText = commandText;
                    if (parameterNames != null && parameterNames.Length > 0)
                    {
                        string[] parameterParsed = parameterNames.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
                        for (int parameterCounter = 0; parameterCounter < parameterParsed.Length; parameterCounter++)
                        {
                            command.Parameters.Add(new SqlParameter(parameterParsed[parameterCounter], parameterValues[parameterCounter]));
                        }
                    }
                    returnValue = command.ExecuteNonQuery();
                }
                sqlConnection.Close();
            }
            return returnValue;
        }

        /// <summary>
        /// Execute a query command against a database
        /// </summary>
        /// <param name="connectionString">Connection string to use to connect to a database</param>
        /// <param name="commandText">Query text</param>
        /// <param name="commandTypeString">Type of command as a string</param>
        /// <param name="parameterNames">Array of parameters names</param>
        /// <param name="parameterValues">Array of parameter values</param>
        /// <returns>
        /// Array of two strings - one as serialized results set,
        /// the other is a list of columns
        /// </returns>
        public string[] ExecuteQuery(
            string connectionString, string commandText, string commandTypeString,
            string parameterNames, object[] parameterValues)
        {
            CommandType commandType = (CommandType)Enum.Parse(typeof(CommandType), commandTypeString);
            List<object[]> returnValue = new List<object[]>();
            StringBuilder fields = new StringBuilder();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (SqlCommand command = sqlConnection.CreateCommand())
                {
                    command.CommandType = commandType;
                    command.CommandText = commandText;
                    if (parameterNames != null && parameterNames.Length > 0)
                    {
                        string[] parameterParsed = parameterNames.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
                        for (int parameterCounter = 0; parameterCounter < parameterParsed.Length; parameterCounter++)
                        {
                            command.Parameters.Add(new SqlParameter(parameterParsed[parameterCounter], parameterValues[parameterCounter]));
                        }
                    }

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        foreach (DataRow oneRow in reader.GetSchemaTable().Rows)
                        {
                            fields.Append(oneRow[0].ToString());
                            fields.Append("|");
                        }
                        while (reader.Read())
                        {
                            object[] data = new object[reader.FieldCount];
                            int columns = reader.GetValues(data);
                            returnValue.Add(data);
                        }
                    }
                    sqlConnection.Close();
                }
                XmlSerializer serializer = new XmlSerializer(returnValue.ToArray().GetType());
                System.IO.MemoryStream aMemStr = new System.IO.MemoryStream();
                System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(aMemStr, null);
                serializer.Serialize(writer, returnValue.ToArray());
                string strXml = System.Text.Encoding.UTF8.GetString(aMemStr.ToArray());
                string[] returnData = new string[2];
                returnData[0] = strXml;
                returnData[1] = fields.ToString();
                return returnData;
            }
        }

        /// <summary>
        /// Execute scalar query command against a database
        /// </summary>
        /// <param name="connectionString">Connection string to use to connect to a database</param>
        /// <param name="commandText">Query text</param>
        /// <param name="commandTypeString">Type of command as a string</param>
        /// <param name="parameterNames">Array of parameters names</param>
        /// <param name="parameterValues">Array of parameter values</param>
        /// <returns>Scalar value from the query</returns>
        public object ExecuteScalar(
            string connectionString, string commandText, string commandTypeString,
            string parameterNames, object[] parameterValues)
        {
            CommandType commandType = (CommandType)Enum.Parse(typeof(CommandType), commandTypeString);
            object returnValue = null;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (SqlCommand command = sqlConnection.CreateCommand())
                {
                    command.CommandType = commandType;
                    command.CommandText = commandText;
                    if (parameterNames != null && parameterNames.Length > 0)
                    {
                        string[] parameterParsed = parameterNames.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
                        for (int parameterCounter = 0; parameterCounter < parameterParsed.Length; parameterCounter++)
                        {
                            command.Parameters.Add(new SqlParameter(parameterParsed[parameterCounter], parameterValues[parameterCounter]));
                        }
                    }
                    returnValue = command.ExecuteScalar();
                }
                sqlConnection.Close();
            }
            return returnValue;
        }
    }
}
